import datetime
from time import sleep
import openpyxl
import mqtt_client as mqtt_client
from os import path
from json import dumps
import logging
import logging.config
import logging.handlers

# 忽略的sheet
ignordsheets = ["平台参数", "遥测列表", "信息说明", "已删除设备"]
# 要上传的key值
keyList = ["Num", "Location", "InstallTime", "Encode", "Brand", "UpperAssets", "DelegateMeter", "LoadPower",
           "UpperDevs", "LoadSEER", "IPLV", "WaterType", "Direction", "Type", "LoadCOP", "RatedCoolingCapacity",
           "LoadLift", "RatedFlow", "HumRange", "Latitude", "Longitude", "Model", "SerialNumber", "FirmwareVersion",
           "Port", "Module", "MaxValue", "Remark"]

_log = logging.getLogger("config_tool")


# 更新客户端属性
class UpdateAttributeTool:

    def __init__(self):
        self.creatLog()
        # 当前目录
        self.cur_path = path.dirname(path.abspath(__file__))
        # 打开文件当设备信息.xlsx
        self.wb = openpyxl.load_workbook(
            self.cur_path + "/config/设备列表.xlsx".replace("/", path.sep))
        # 获取需要上传数据的sheet名
        self.sheet_names = []
        for sheet in self.wb.sheetnames:
            if sheet not in ignordsheets:
                self.sheet_names.append(sheet)
        # 获取名字为"平台参数"sheet
        self.pconf_sheet = self.wb['平台参数']
        # 第一列为key，第二列为value
        self.config = {}
        for row in self.pconf_sheet.rows:
            if row[0].value is None:
                break
            self.config[row[0].value] = row[1].value
        self.mqtt_client = mqtt_client.MqttClient(self.config)

    def creatLog(self):
        logging.config.fileConfig(
            path.dirname(path.dirname(path.abspath(__file__))) + "/config/logs.conf", disable_existing_loggers=False)
        global _log

    def load_device_info(self):
        message = {}
        for sheet in self.sheet_names:
            resule = self._dev_info(self.wb[sheet])
            if resule is not None:
                for key in resule:
                    message[key] = resule[key]
        return message

    def get_firstname(self, name):
        # 去除中文括号或者英文括号内内容
        if "（" in name:
            name = name.split("（")[0]
        if "(" in name:
            name = name.split("(")[0]
        # 去除空格
        name = name.replace(" ", "")
        return name

    def _dev_info(self, sheet):
        dev_info = {}
        key_list = []
        # 第一行的值为key
        for cell in sheet[1]:
            if cell.value is None:
                continue
            key_list.append(self.get_firstname(cell.value))
        # 从第二行开始，每一列的值为value
        if len(key_list) == 0:
            return
        for row in sheet.iter_rows(min_row=2, min_col=1):
            for i in range(len(key_list)):
                if row[0].value is None:
                    continue
                if row[0].value not in dev_info:
                    dev_info[row[0].value] = {}
                if key_list[i] not in keyList:
                    continue
                # 日期类型转换为字符串
                if type(row[i].value) == type(datetime.datetime.now()):
                    dev_info[row[0].value][key_list[i]] = str(row[i].value).split(" ")[0]
                else:
                    dev_info[row[0].value][key_list[i]] = str(row[i].value)
                # 如果为空则跳过
                if dev_info[row[0].value][key_list[i]] == "None":
                    del dev_info[row[0].value][key_list[i]]

        return dev_info

    def start(self):
        self.mqtt_client.start()
        _log.info("starting")
        m = self.load_device_info()
        # 输出到payload.json,中文不乱码
        with open("payload.json", "w", encoding="utf-8") as f:
            f.write(dumps(m, indent=4, ensure_ascii=False))
        for key, value in m.items():
            self.mqtt_client.send_msg({key: value})
            sleep(0.1)
        _log.info("发送完成")


if __name__ == '__main__':
    updateTool = UpdateAttributeTool()
    updateTool.start()
